package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Entity;
import pos.domain.EntityImpl;
import pos.domain.Event;
import pos.domain.SubType;
import pos.domain.Type;
import util.UIDGenerator;

public class JDBCEntityDAO implements IEntityDAO {
	
	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCEntityDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	
	public List<Entity> getAllEntities() {

		String sql = "SELECT * FROM entity";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Entity> listEntities = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listEntities = new ArrayList<Entity>();

			while (result.next()) {
				Entity entity = new EntityImpl();
				entity.setId(result.getString("identity"));
				entity.setName(result.getString("name"));
				Type type =new JDBCTypeDAO().getTypeByEntity(result.getString("identity"));
				entity.setType(type);
				entity.setListSubType(type.getListSubTypes());
				List<String> listRDFLinks = new ArrayList<String>();
//				website,dbpedia,yago,opencyc,umbel,freebase,ciaFactbook,census,geonames,musicBrainz,crunchbase,semanticCrunchbase\
				String dbpedia = result.getString("dbpedia");
				String yago = result.getString("yago");
				String opencyc = result.getString("opencyc");
				String umbel = result.getString("umbel");
				String freebase = result.getString("freebase");
				String ciafactbook = result.getString("ciafactbook");
				String census = result.getString("census");
				String geonames = result.getString("geonames");
				String musicbrainz = result.getString("musicbrainz");
				String crunchbase = result.getString("crunchbase");
				String semanticcrunchbase = result.getString("semanticcrunchbase");
				String website = result.getString("website");

				if(dbpedia!=null){
					listRDFLinks.add(dbpedia);
				}
				if(yago!=null){
					listRDFLinks.add(yago);
				}
				if(opencyc!=null){
					listRDFLinks.add(opencyc);
				}
				if(umbel!=null){
					listRDFLinks.add(umbel);
				}
				if(freebase!=null){
					listRDFLinks.add(freebase);
				}
				if(ciafactbook!=null){
					listRDFLinks.add(ciafactbook);
				}
				if(census!=null){
					listRDFLinks.add(census);
				}
				if(geonames!=null){
					listRDFLinks.add(geonames);
				}
				if(musicbrainz!=null){
					listRDFLinks.add(musicbrainz);
				}
				if(crunchbase!=null){
					listRDFLinks.add(crunchbase);
				}
				if(semanticcrunchbase!=null){
					listRDFLinks.add(semanticcrunchbase);
				}
				
				if(website!=null){
					listRDFLinks.add(website);
				}
				if(!listRDFLinks.isEmpty())
					entity.setListRDFLinks(listRDFLinks);
				
				listEntities.add(entity);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listEntities;
	}

	public Entity insertEntity(Entity entity, Event event) {
		
		PreparedStatement stmt = null;
		String idEntity = "";
		if(entity.getId()==null){
			idEntity = UIDGenerator.getInstance().getKey(); // ID for DB
			entity.setId(idEntity); // ID for object
		}
		idEntity = entity.getId();

		String sql = "INSERT INTO entity (identity,name,idevent,dbpedia,yago,opencyc,umbel,freebase,ciafactbook,census,geonames,musicbrainz,crunchbase,semanticcrunchbase,website,uri) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, idEntity);
			stmt.setString(2, entity.getName());
			stmt.setString(3, event.getId()); // ojo a partir de aquí al insertar entidades.
			stmt.setString(4, null);
			stmt.setString(5, null);
			stmt.setString(6, null);
			stmt.setString(7, null);
			stmt.setString(8, null);
			stmt.setString(9, null);
			stmt.setString(10, null);
			stmt.setString(11, null);
			stmt.setString(12, null);
			stmt.setString(13, null);
			stmt.setString(14, null);
			stmt.setString(15, null);
			stmt.setString(16, entity.getUri());
			
			if(entity.getListRDFLinks()!=null){
				for(String s: entity.getListRDFLinks()){
					if(s.contains("dbpedia")){
						stmt.setString(4, s);
					} else if(s.contains("yago")){
						stmt.setString(5, s);
					} else if(s.contains("opencyc")){
						stmt.setString(6, s);
					} else if(s.contains("umbel")){
						stmt.setString(7, s);
					} else if(s.contains("freebase")){
						stmt.setString(8, s);
					} else if(s.contains("ciafactbook")){
						stmt.setString(9, s);
					} else if(s.contains("census")){
						stmt.setString(10, s);
					} else if(s.contains("geonames")){
						stmt.setString(11, s);
					} else if(s.contains("musicbrainz")){
						stmt.setString(12, s);
					} else if(s.contains("crunchbase")){
						stmt.setString(13, s);
					} else if(s.contains("semanticcrunchbase")){
						stmt.setString(14, s);
					} else if(s.contains("website")){
						stmt.setString(15,s);
					}
						
				}
			}
			
			stmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();

		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return entity;
		
	}

	public Entity getEntityById(String idEntity) {

		Entity entity = new EntityImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Entities with that ID.
		String sql = "SELECT * FROM entity WHERE (identity = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEntity);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				entity.setId(result.getString("identity"));
				entity.setName(result.getString("name"));
				Type type = new JDBCTypeDAO().getTypeByEntity(entity.getId());
				entity.setType(type);
				entity.setListSubType(type.getListSubTypes());
				List<String> listRDFLinks = new ArrayList<String>();
//				website,dbpedia,yago,opencyc,umbel,freebase,ciaFactbook,census,geonames,musicBrainz,crunchbase,semanticCrunchbase\
				String dbpedia = result.getString("dbpedia");
				String yago = result.getString("yago");
				String opencyc = result.getString("opencyc");
				String umbel = result.getString("umbel");
				String freebase = result.getString("freebase");
				String ciafactbook = result.getString("ciafactbook");
				String census = result.getString("census");
				String geonames = result.getString("geonames");
				String musicbrainz = result.getString("musicbrainz");
				String crunchbase = result.getString("crunchbase");
				String semanticcrunchbase = result.getString("semanticcrunchbase");
				String website = result.getString("website");

				if(dbpedia!=null){
					listRDFLinks.add(dbpedia);
				}
				if(yago!=null){
					listRDFLinks.add(yago);
				}
				if(opencyc!=null){
					listRDFLinks.add(opencyc);
				}
				if(umbel!=null){
					listRDFLinks.add(umbel);
				}
				if(freebase!=null){
					listRDFLinks.add(freebase);
				}
				if(ciafactbook!=null){
					listRDFLinks.add(ciafactbook);
				}
				if(census!=null){
					listRDFLinks.add(census);
				}
				if(geonames!=null){
					listRDFLinks.add(geonames);
				}
				if(musicbrainz!=null){
					listRDFLinks.add(musicbrainz);
				}
				if(crunchbase!=null){
					listRDFLinks.add(crunchbase);
				}
				if(semanticcrunchbase!=null){
					listRDFLinks.add(semanticcrunchbase);
				}
				if(website!=null){
					listRDFLinks.add(website);
				}
				if(!listRDFLinks.isEmpty())
					entity.setListRDFLinks(listRDFLinks);
				
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return entity;
	}
	

	public List<SubType> getListSubTypesOfEntity(Entity entity) {

		return new JDBCSubTypeDAO().getListSubTypesByEntity(entity.getId());
	}

	public void removeEntity(Entity entity) {

		PreparedStatement stmt = null;
		
		// Also remove the associations with Events and Types? We want data.

		String sql = "DELETE FROM entity WHERE (identity = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, entity.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
		
	}

	public List<Entity> getListEntitiesByEvent(String idEvent) {

		List<Entity> listEntities = new ArrayList<Entity>();
		List<SubType> ls = null;
		PreparedStatement stm = null;
		ResultSet result = null;
		String sql = "SELECT * FROM entity WHERE (idevent = ?)";

		try {
			stm = conn.prepareStatement(sql);
			stm.setString(1, idEvent);
			result = stm.executeQuery();
			while (result.next()) {
				String identity = result.getString("identity");
				Entity entity = new EntityImpl();
				entity.setId(identity);
				entity.setName(result.getString("name"));
				Type type = new JDBCTypeDAO().getTypeByEntity(identity);
				if((ls = new JDBCSubTypeDAO().getListSubTypesByEntity(identity))!=null){
					type.setListSubTypes(ls);
					entity.setListSubType(ls);
					entity.setType(type);
				}
				

				List<String> listRDFLinks = new ArrayList<String>();
//				website,dbpedia,yago,opencyc,umbel,freebase,ciaFactbook,census,geonames,musicBrainz,crunchbase,semanticCrunchbase\
				String dbpedia = result.getString("dbpedia");
				String yago = result.getString("yago");
				String opencyc = result.getString("opencyc");
				String umbel = result.getString("umbel");
				String freebase = result.getString("freebase");
				String ciafactbook = result.getString("ciafactbook");
				String census = result.getString("census");
				String geonames = result.getString("geonames");
				String musicbrainz = result.getString("musicbrainz");
				String crunchbase = result.getString("crunchbase");
				String semanticcrunchbase = result.getString("semanticcrunchbase");
				String website = result.getString("website");

				if(dbpedia!=null){
					listRDFLinks.add(dbpedia);
				}
				if(yago!=null){
					listRDFLinks.add(yago);
				}
				if(opencyc!=null){
					listRDFLinks.add(opencyc);
				}
				if(umbel!=null){
					listRDFLinks.add(umbel);
				}
				if(freebase!=null){
					listRDFLinks.add(freebase);
				}
				if(ciafactbook!=null){
					listRDFLinks.add(ciafactbook);
				}
				if(census!=null){
					listRDFLinks.add(census);
				}
				if(geonames!=null){
					listRDFLinks.add(geonames);
				}
				if(musicbrainz!=null){
					listRDFLinks.add(musicbrainz);
				}
				if(crunchbase!=null){
					listRDFLinks.add(crunchbase);
				}
				if(semanticcrunchbase!=null){
					listRDFLinks.add(semanticcrunchbase);
				}
				if(website!=null){
					listRDFLinks.add(website);
				}
				if(!listRDFLinks.isEmpty())
					entity.setListRDFLinks(listRDFLinks);
				
				listEntities.add(entity);
			}

		} catch (SQLException e) {
			System.out.println("SQLMessage: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stm != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return listEntities;
	}

	public boolean existEntity(String name, String idevent) {
		
		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM entity WHERE (name = ?) AND (idevent =?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, idevent);
			result = stmt.executeQuery();

			if (result.next()==false) {
				res = false;
			} else {
				res = true;
		}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;

	}

}
